package com.cisco.nm.pf.dbservice.resultbuilder;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

import com.cisco.nm.pf.dbservice.dbutil.DatabaseUtil;

public final class DBResultBuilder extends DatabaseUtil	{
	
	private static DatabaseUtil dbUtil = new DBResultBuilder();
	
	public DBResultBuilder() {
		// TODO Auto-generated constructor stub
	}
	/**
	 * Api to retrieve data from database
	 * 
	 */
	public JSONObject executeQuery(Map<String,String> usermap,String query){
		
		Connection con = getConnection(usermap);
		Statement stm = null;
		ResultSet res = null;
		JSONObject dbresult = null;
		JSONArray header = null;
		JSONArray dataGrid = null;
		try {
			//1. execute query
			stm = con.createStatement();
			res = stm.executeQuery(query);
			
			//2. retrieve the meta data from the result set
			//ie the column name and column type.
			
			ResultSetMetaData metadata = res.getMetaData();
			int columnCount =metadata.getColumnCount();

			header = new JSONArray();
			for (int i = 1; i <= columnCount; i++) {
				JSONObject obj = new JSONObject();
				obj.put("colName",metadata.getColumnName(i));
				obj.put("colType", metadata.getColumnTypeName(i));
				header.put(obj);
			}
			System.out.println("Column Count = " + columnCount);
			dataGrid = new JSONArray();
			JSONArray row = new JSONArray();
			int i =1;
			int rowCount = 0;
			while(res.next()){
				if(i == columnCount){
					i = 1;
					dataGrid.put(row);
					row = new JSONArray();
					rowCount ++;
				}
				row.put(res.getObject(i));
				i++;
				
			}
			System.out.println("No of Rows retrieved = " + rowCount);
			dbresult = new JSONObject();
			dbresult.put("HEADER",header);
			dbresult.put("RESULT",dataGrid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally	{
			close(con);
		}
		
		return dbresult;
	}
	public static DatabaseUtil getDBUtil(){
		return dbUtil;
	}
	
}
